﻿using Domain.Entitys.Sinv;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace API.Base
{
    public class ExeclHelper
    {

        public static MemoryStream Export(
            string[] headers,
            IEnumerable<object[]> rows,
            string title,
            string[] mergeColumns,
            bool showTitle = true,
            Type[] columnTypes = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);
                int maxColumnCount = headers.Length;
                int curRowIndex = 0;

                // 1. 标题区域（可选）
                if (showTitle)
                {
                    curRowIndex++;
                    workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true;
                    workSheet.Cells[curRowIndex, 1].Value = title;
                    workSheet.Cells[curRowIndex, 1].Style.Font.Bold = true;
                    workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    curRowIndex++;
                    workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true;
                    workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
                    workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    workSheet.Cells[curRowIndex, 1].Style.Font.Italic = true;
                }

                // 2. 列标题
                curRowIndex++;
                for (int i = 0; i < maxColumnCount; i++)
                {
                    workSheet.Cells[curRowIndex, i + 1].Value = headers[i];
                    workSheet.Cells[curRowIndex, i + 1].Style.Font.Bold = true;
                    workSheet.Cells[curRowIndex, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    workSheet.Cells[curRowIndex, i + 1].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                }
                workSheet.View.FreezePanes(curRowIndex + 1, 1); // 冻结标题行

                // 3. 数据行
                int dataStartRow = curRowIndex + 1; // 记录数据起始行
                List<object[]> dataRows = rows.ToList(); // 转换为列表以便多次访问

                foreach (var rowData in dataRows)
                {
                    curRowIndex++;
                    for (int j = 0; j < maxColumnCount; j++)
                    {
                        var cell = workSheet.Cells[curRowIndex, j + 1];
                        object value = (j < rowData.Length) ? rowData[j] : null;

                        // 数据类型处理
                        if (columnTypes != null && j < columnTypes.Length)
                        {
                            var colType = Nullable.GetUnderlyingType(columnTypes[j]) ?? columnTypes[j];
                            if (value != null && value != DBNull.Value)
                            {
                                if (colType == typeof(DateTime))
                                {
                                    cell.Style.Numberformat.Format = "yyyy-MM-dd HH:mm";
                                    cell.Value = Convert.ToDateTime(value);
                                }
                                else if (colType == typeof(int))
                                {
                                    cell.Value = Convert.ToInt32(value);
                                }
                                else if (colType == typeof(double) || colType == typeof(decimal))
                                {
                                    cell.Value = Convert.ToDouble(value);
                                }
                                else
                                {
                                    cell.Value = value.ToString();
                                }
                            }
                            else
                            {
                                cell.Value = ""; // 显式设置空值
                            }
                        }
                        else
                        {
                            cell.Value = value?.ToString() ?? "";
                        }
                    }
                }

                int lastDataRow = curRowIndex;

                // 4. 合并处理（关键修改）
                if (mergeColumns != null && mergeColumns.Length > 0)
                {
                    // 获取需要合并的列索引
                    List<int> mergeColIndexes = new List<int>();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        if (mergeColumns.Contains(headers[i]))
                        {
                            mergeColIndexes.Add(i + 1); // 列索引从1开始
                        }
                    }

                    // 按列进行合并
                    foreach (int col in mergeColIndexes)
                    {
                        // 存储合并区域的起始行
                        Dictionary<int, int> mergeRegions = new Dictionary<int, int>();

                        // 第一遍：识别合并区域
                        int? currentGroupStart = null;
                        for (int row = dataStartRow; row <= lastDataRow; row++)
                        {
                            var cell = workSheet.Cells[row, col];
                            bool isValueCell = !IsEmpty(cell.Value);

                            // 如果是值单元格（非空）
                            if (isValueCell)
                            {
                                // 如果当前有正在处理的区域，结束它
                                if (currentGroupStart.HasValue)
                                {
                                    mergeRegions[currentGroupStart.Value] = row - 1;
                                }

                                // 开始新的合并区域
                                currentGroupStart = row;
                            }

                            // 处理最后一行
                            if (row == lastDataRow && currentGroupStart.HasValue)
                            {
                                mergeRegions[currentGroupStart.Value] = lastDataRow;
                            }
                        }

                        // 第二遍：执行合并
                        foreach (var region in mergeRegions)
                        {
                            int startRow = region.Key;
                            int endRow = region.Value;

                            // 只有需要合并多行时才执行
                            if (endRow > startRow)
                            {
                                var mergeRange = workSheet.Cells[startRow, col, endRow, col];
                                mergeRange.Merge = true;
                                mergeRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                mergeRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                                // 确保合并后保留原始值
                                if (workSheet.Cells[startRow, col].Value == null)
                                {
                                    mergeRange.Value = workSheet.Cells[startRow, col].Value;
                                }
                            }
                        }
                    }
                }

                // 5. 样式优化
                workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体";
                workSheet.Cells[workSheet.Dimension.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                workSheet.Cells[workSheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
                for (int i = 1; i <= maxColumnCount; i++)
                {
                    workSheet.Column(i).Width = 20;
                }

                return new MemoryStream(package.GetAsByteArray());
            }
        }

        // 辅助方法：合并单元格并设置居中样式
        private static void MergeIfValid(ExcelWorksheet sheet, int startRow, int endRow, int col)
        {
            if (endRow > startRow) // 至少2行才合并
            {
                var mergeRange = sheet.Cells[startRow, col, endRow, col];
                mergeRange.Merge = true;

                // 设置合并后单元格的样式
                mergeRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                mergeRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                // 恢复原始值（合并后只保留左上角的值）
                if (sheet.Cells[startRow, col].Value == null)
                {
                    mergeRange.Value = sheet.Cells[startRow, col].Value;
                }
            }
        }

        // 辅助方法：判断单元格是否为空
        private static bool IsEmpty(object value)
        {
            return value == null ||
                   value is DBNull ||
                   (value is string str && string.IsNullOrWhiteSpace(str));
        }

    }
}
